package main

import (
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

type user struct {
	id        int
	username  string
	password  string
	createdAt time.Time
}

func main() {

	db, err := sql.Open("mysql", "root:0000abc!@(127.0.0.1:3306)/gotestdb?parseTime=true")

	if err != nil {
		log.Fatal(err)
	}

	if err := db.Ping(); err != nil {
		log.Fatal(err)
	}
	{
		//////create a new table
		query := `
			CREATE TABLE IF NOT EXISTS users(
				id INT AUTO_INCREMENT,
				username TEXT NOT NULL,
				password TEXT NOT NULL,
				created_at DATETIME,
				PRIMARY KEY (id)
			)
		`

		if _, err := db.Exec(query); err != nil {
			log.Fatal(err)
		} else {
			fmt.Printf("create table users success\n")
		}
	}
	{
		/////////insert into table
		usrname := "johndoe"
		pwd := "secret"
		createdAt := time.Now()

		if row, err := db.Exec(`INSERT INTO users (username,password,created_at) VALUES (?,?,?)`, usrname, pwd, createdAt); err != nil {
			log.Fatal(err)
		} else {
			if id, err := row.LastInsertId(); err != nil {
				log.Fatal(err)
			} else {
				fmt.Printf("insert success -> row id: %d\n", id)
			}
		}
	}
	{
		//////select a record
		var (
			id       int
			username string
			password string
			createAt time.Time
		)

		if err := db.QueryRow(`SELECT id, username, password, created_at FROM users WHERE id = ?`, 1).Scan(&id, &username, &password, &createAt); err != nil {
			log.Fatal(err)
		} else {
			fmt.Printf("got row id:%d, username:%s, password:%s,createAt:%s\n", id, username, password, createAt)
		}
	}
	{
		/////select all users
		if rows, err := db.Query(`SELECT id, username, password, created_at FROM users`); err != nil {
			log.Fatal(err)
		} else {
			defer rows.Close()
			var users []user
			for rows.Next() {
				var u user
				if err := rows.Scan(&u.id, &u.username, &u.password, &u.createdAt); err != nil {
					log.Fatal(err)
				} else {
					users = append(users, u)
					fmt.Print("find all users %#v", users)
				}
			}
		}
	}

	{
		/////////delete user(s)
		id := 1
		if _, err := db.Exec(`DELETE FROM users WHERE id = ?`, id); err != nil {
			log.Fatal(err)
		} else {
			fmt.Printf("delete user with id: %d successfully\n", id)
		}
	}

}
